package carassius.DAL;

import carassius.BLL.Events.OrganismList.OrganismListAddedListener;
import carassius.BLL.Events.OrganismList.OrganismListAddedPublisher;
import carassius.BLL.Events.OrganismList.OrganismListRemovedListener;
import carassius.BLL.Events.OrganismList.OrganismListRemovedPublisher;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class OrganismListTable
		extends Table<OrganismListRow, SimpleEntry<Integer, Integer>>
		implements OrganismListAddedPublisher,
				   OrganismListRemovedPublisher
{
	//<editor-fold defaultstate="collapsed" desc="Fields">
	private final ArrayList<OrganismListAddedListener> _organismListAddedListeners;
	private final ArrayList<OrganismListRemovedListener> _organismListRemovedListeners;
	private final String SQL_ADD = "INSERT INTO organism_list VALUES ( ? , ? , ?)",
			SQL_DELETE = "DELETE FROM organism_list WHERE aquarium_id = ? AND organism_id = ?",
			SQL_DELETE_BY_ORGANISM_ID = "DELETE FROM organism_list WHERE organism_id = ?",
			SQL_DELETE_BY_AQAURIUM_ID = "DELETE FROM organism_list WHERE aquarium_id = ?",
			SQL_GET_ALL = "SELECT * FROM organism_list",
			SQL_GET_BY_AQUARIUM_ID = "SELECT * FROM organism_list WHERE aquarium_id = ?",
			SQL_GET_BY_ORGANISM_ID = "SELECT * FROM organism_list WHERE organism_id = ?",
			SQL_GET_BY_AQUARIUM_ID_AND_ORGANISM_ID = "SELECT * FROM organism_list WHERE aquarium_id = ? AND organism_id = ?";
	private boolean _allCached;
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Constructors">
	public OrganismListTable()
	{
		_organismListAddedListeners = new ArrayList<OrganismListAddedListener>();
		_organismListRemovedListeners = new ArrayList<OrganismListRemovedListener>();
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL add">
	public OrganismListRow add(AquariumRow aquariumRow, OrganismRow organismRow,
							   Integer amount) throws SQLException
	{
		return add(aquariumRow.getId(), organismRow.getId(), amount);
	}

	public OrganismListRow add(Integer aquariumId, Integer organismId,
							   Integer amount) throws SQLException
	{
		SimpleEntry<Integer, Integer> keys = add(new Object[]
				{
					aquariumId, organismId, amount
				});
		if (keys != null)
		{
			return getByPrimaryKey(keys);
		}
		return null;
	}

	@Override
	public SimpleEntry<Integer, Integer> add(Object[] args) throws SQLException
	{
		SimpleEntry<Integer, Integer> out = null;
		if (args != null
				&& args.length >= 3
				&& ((args[0] instanceof Integer && (Integer) args[0] >= 0)
				|| ((args[0] instanceof AquariumRow) && ((AquariumRow) args[0]).getId() >= 0))
				&& args[1] instanceof Integer
				&& (Integer) args[1] >= 0
				&& args[2] instanceof Integer
				&& (Integer) args[2] > 0)
		{
			out = new SimpleEntry<Integer, Integer>((args[0] instanceof AquariumRow)
					? ((AquariumRow) args[0]).getId() : (Integer) args[0],
					(Integer) args[1]);
			PreparedStatement st = Database.prepareStatement(SQL_ADD);
			st.setInt(1, out.getKey()); //aquarium id
			st.setInt(2, out.getValue()); //organism id
			st.setObject(3, args[2], Types.INTEGER); //amount

			try
			{
				synchronized (getRows())
				{
					if (st.executeUpdate() == 1)
					{
						_allCached = false;
						OrganismListRow organismListRow = getByPrimaryKey(out);
						_allCached = true;
						fireOrganismListAddedEvent(organismListRow);
					}
					else
					{
						out = null;
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return out;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(SimpleEntry<Integer, Integer> keys) throws SQLException
	{
		boolean result = false;
		if (keys != null
				&& keys.getKey() != null
				&& keys.getKey() >= 0
				&& keys.getValue() != null
				&& keys.getValue() >= 0)
		{
			synchronized (getRows())
			{
				PreparedStatement st = Database.prepareStatement(SQL_DELETE);
				st.setInt(1, keys.getKey());
				st.setInt(2, keys.getValue());

				try
				{
					OrganismListRow organismListRow = getByPrimaryKey(keys);
					if (st.executeUpdate() == 1)
					{
						result = true;
						getRows().remove(keys);
						fireOrganismListRemovedEvent(organismListRow);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}

	public boolean deleteByOrganismID(int id) throws SQLException
	{
		boolean result = false;
		if (id >= 0)
		{
			synchronized (getRows())
			{
				PreparedStatement st = Database.prepareStatement(SQL_DELETE_BY_ORGANISM_ID);
				st.setInt(1, id);

				try
				{
					Collection<OrganismListRow> removeQueue = getByOrganismId(id);
					if (st.executeUpdate() == removeQueue.size())
					{
						result = true;
						for (OrganismListRow organismListRow : removeQueue)
						{
							getRows().remove(organismListRow.getPrimaryKeys());
							fireOrganismListRemovedEvent(organismListRow);
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}

	public boolean deleteByAquariumID(int id) throws SQLException
	{
		boolean result = false;
		if (id >= 0)
		{
			synchronized (getRows())
			{
				PreparedStatement st = Database.prepareStatement(SQL_DELETE_BY_AQAURIUM_ID);
				st.setInt(1, id);

				try
				{
					Collection<OrganismListRow> removeQueue = getByAquariumId(id);
					if (st.executeUpdate() == removeQueue.size())
					{
						result = true;
						for (OrganismListRow organismListRow : removeQueue)
						{
							getRows().remove(organismListRow.getPrimaryKeys());
							fireOrganismListRemovedEvent(organismListRow);
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	@Override
	public Collection<OrganismListRow> getAll() throws SQLException
	{
		PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

		synchronized (getRows())
		{
			if (!_allCached)
			{
				try
				{
					ResultSet query = st.executeQuery();
					getRows().clear();

					while (query.next())
					{
						put(new OrganismListRow(query));
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	public ArrayList<OrganismListRow> getByAquariumId(int id) throws SQLException
	{
		ArrayList<OrganismListRow> out = null;
		if (id >= 0)
		{
			out = new ArrayList<OrganismListRow>();

			PreparedStatement st = Database.prepareStatement(SQL_GET_BY_AQUARIUM_ID);
			st.setInt(1, id);

			synchronized (getRows())
			{
				try
				{
					if (_allCached)
					{
						for (OrganismListRow organismListRow : getRows().values())
						{
							if (organismListRow.getAquarium().getId() == id)
							{
								out.add(organismListRow);
							}
						}
					}
					else
					{
						ResultSet query = st.executeQuery();

						while (query.next())
						{
							OrganismListRow organismListRow = new OrganismListRow(query);
							put(organismListRow);
							out.add(organismListRow);
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}

	public ArrayList<OrganismListRow> getByOrganismId(int id) throws SQLException
	{
		ArrayList<OrganismListRow> out = null;
		if (id >= 0)
		{
			out = new ArrayList<OrganismListRow>();

			PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ORGANISM_ID);
			st.setInt(1, id);

			try
			{
				synchronized (getRows())
				{
					if (_allCached)
					{
						for (OrganismListRow organismListRow : getRows().values())
						{
							if (organismListRow.getOrganismId() == id)
							{
								out.add(organismListRow);
							}
						}
					}
					else
					{
						ResultSet query = st.executeQuery();

						while (query.next())
						{
							OrganismListRow organismListRow = new OrganismListRow(query);
							put(organismListRow);
							out.add(organismListRow);
						}
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return out;
	}

	@Override
	public OrganismListRow getByPrimaryKey(SimpleEntry<Integer, Integer> key) throws SQLException
	{
		OrganismListRow row = null;
		if (key != null
				&& key.getKey() != null
				&& key.getKey() >= 0
				&& key.getValue() != null
				&& key.getValue() >= 0)
		{
			synchronized (getRows())
			{
				row = getRows().get(key);
				if (row != null || _allCached)
				{
					return row;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_AQUARIUM_ID_AND_ORGANISM_ID);
				st.setInt(1, key.getKey());
				st.setInt(2, key.getValue());

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						row = new OrganismListRow(query);
						put(row);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return row;
	}
	//</editor-fold>
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Events">
	//<editor-fold defaultstate="collapsed" desc="Added">
	@Override
	public void addOrganismListAddedEventListener(
			OrganismListAddedListener listener)
	{
		synchronized (_organismListAddedListeners)
		{
			_organismListAddedListeners.add(listener);
		}
	}

	@Override
	public void removeOrganismListAddedEventListener(
			OrganismListAddedListener listener)
	{
		synchronized (_organismListAddedListeners)
		{
			_organismListAddedListeners.remove(listener);
		}
	}

	private void fireOrganismListAddedEvent(OrganismListRow row)
	{
		synchronized (_organismListAddedListeners)
		{
			for (OrganismListAddedListener listener : _organismListAddedListeners)
			{
				listener.organismListAdded(row);
			}
		}
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Removed">
	@Override
	public void addOrganismListRemovedEventListener(
			OrganismListRemovedListener listener)
	{
		synchronized (_organismListRemovedListeners)
		{
			_organismListRemovedListeners.add(listener);
		}
	}

	@Override
	public void removeOrganismListRemovedEventListener(
			OrganismListRemovedListener listener)
	{
		synchronized (_organismListRemovedListeners)
		{
			_organismListRemovedListeners.remove(listener);
		}
	}

	private void fireOrganismListRemovedEvent(OrganismListRow row)
	{
		synchronized (_organismListRemovedListeners)
		{
			for (OrganismListRemovedListener listener : _organismListRemovedListeners)
			{
				listener.organismListRemoved(row);
			}
		}
	}
	//</editor-fold>
	//</editor-fold>
}
